from datetime import datetime
from typing import List, Dict, Any, Optional

import pymysql
# import MySQLdb

from django.conf import settings

class DatabaseManager:
    def __init__(self):
        self.db_config = {
            'host': '192.168.20.42',
            'user': 'dbadmin',
            'passwd': 'Qweqwe123',
            'db': 'model_cryogenic_test_platform'
        }

    def get_connection(self):
        """获取数据库连接"""
        try:
            connection = pymysql.connect(**self.db_config)
            return connection
        except Exception as e:
            print(f"数据库连接失败: {str(e)}")
            raise

    def execute_query(self, query: str, params: tuple = None) -> List[Dict[str, Any]]:
        """执行查询并返回结果"""
        connection = None
        cursor = None
        try:
            connection = self.get_connection()
            cursor = connection.cursor(pymysql.cursors.DictCursor)
            cursor.execute(query, params)
            return cursor.fetchall()
        except Exception as e:
            print(f"查询执行失败: {str(e)}")
            raise
        finally:
            if cursor:
                cursor.close()
            if connection:
                connection.close()

    def get_experiments(self) -> List[Dict[str, Any]]:
        """获取实验列表"""
        query = """
            SELECT experiment_id, experiment_name 
            FROM cryogenic_test_platform_dim_experiment_stats_list
        """
        return self.execute_query(query)

    def get_experiment_by_id(self, experiment_id: str) -> Optional[Dict[str, Any]]:
        """根据实验ID获取实验信息"""
        query = """
            SELECT experiment_id, experiment_name 
            FROM cryogenic_test_platform_dim_experiment_stats_list 
            WHERE experiment_id = %s
        """
        results = self.execute_query(query, (experiment_id,))
        return results[0] if results else None

    def get_experiment_time_range(self, experiment_id: str) -> Optional[Dict[str, Any]]:
        """获取实验的开始和结束时间"""
        query = """
            SELECT start_time, end_time
            FROM cryogenic_test_platform_dim_experiment_stats_list
            WHERE experiment_id = %s
        """
        results = self.execute_query(query, (experiment_id,))
        return results[0] if results else None

    def get_experiment_channels(self, experiment_id: str) -> List[Dict[str, Any]]:
        """获取实验的所有通道名称"""
        query = """
            SELECT DISTINCT channel_name
            FROM cryogenic_test_platform_dim_device_channel_info_list
            WHERE experiment_id = %s
            ORDER BY channel_name
        """
        return self.execute_query(query, (experiment_id,))

    def get_channel_data(self, experiment_id, channel_name, start_time, end_time, num_points=1600):
        """获取通道数据"""
        connection = self.get_connection()
        try:
            with connection.cursor() as cursor:
                # 调用存储过程
                cursor.callproc('GetChannelStatsTest', (
                    experiment_id,
                    channel_name,
                    start_time,
                    end_time,
                    num_points
                ))
                
                # 获取结果
                results = cursor.fetchall()

                if results and isinstance(results[0], tuple) and len(results[0]) == 4:
                    # time_interval = (end_time - start_time) / (len(results) * 2 - 1)  # 因为每个点都会产生两个值
                    time_interval = (results[1][-1] - results[0][-1]) / 2
                    timestamps = []
                    values = []
                    
                    for i, result in enumerate(results):
                        current_time = results[0][-1] + (i * 2 * time_interval)
                        
                        timestamps.append(current_time)
                        values.append(float(result[1]))  # 最小值
                        
                        timestamps.append(current_time + time_interval)
                        values.append(float(result[2]))  # 最大值
                    
                    return {
                        'timestamps': timestamps,
                        'values': values
                    }
                
                return results

        except pymysql.Error as e:
            raise Exception(f"获取通道数据失败: {str(e)}")
        
        finally:
            connection.close()

    def get_experiment_data(self, experiment_id: str, start_time: str = None, 
                           end_time: str = None, page: int = None, page_size: int = None) -> List[Dict]:
        """
        调用存储过程获取实验数据
        Args:
            experiment_id: 实验ID
            start_time: 开始时间
            end_time: 结束时间
            page: 页码
            page_size: 每页条数
        """
        connection = self.get_connection()
        try:
            with connection.cursor(pymysql.cursors.DictCursor) as cursor:
                # 调用存储过程
                cursor.callproc('GetExperimentDataCombinedLSR', (experiment_id, start_time, end_time, page_size, page))
                
                # 获取结果
                # results = cursor.fetchall()

                # 初始化结果字典
                result_json = {}
                all_results = []  # 存储所有结果集
                
                # 首先获取所有结果集
                while True:
                    results = cursor.fetchall()
                    if results:
                        all_results.append(results)
                    if not cursor.nextset():
                        break
                
                if all_results:
                    # 处理第一个结果集（实验基本信息）
                    first_result = all_results[0][0]
                    result_json.update({
                        'experiment_id': first_result['experiment_id'],
                        'actual_start_time': first_result['actual_start_time'],
                        'actual_end_time': first_result['actual_end_time'],
                        'tables': []
                    })
                    
                    # 获取设备列表
                    device_list = first_result['device_list'].split(',')
                    
                    # 处理剩余的结果集（每个设备的数据）
                    for i, device in enumerate(device_list):
                        if i + 1 < len(all_results):  # 确保还有对应的结果集
                            device_results = all_results[i + 1]
                            if device_results:
                                # 构建表数据
                                table_info = {
                                    'table_name': device,
                                    'columns': list(device_results[0].keys()),
                                    'data': [
                                        list(row.values())
                                        for row in device_results if 'status' not in row
                                    ]
                                }
                                result_json['tables'].append(table_info)
                    
                    # 处理数据时格式化时间戳
                    for table in result_json['tables']:
                        for row in table['data']:
                            if row and len(row) > 0:
                                # 将第一列的时间戳转换为格式化的字符串
                                timestamp = row[0]
                                if timestamp:
                                    try:
                                        # 转换时间戳为datetime对象，然后格式化
                                        dt = datetime.fromtimestamp(float(timestamp))
                                        row[0] = dt.strftime('%Y-%m-%d %H:%M:%S')
                                    except (ValueError, TypeError):
                                        ...
                
                # 解析设备行数信息
                device_counts = {}
                if first_result['device_row_counts']:
                    counts = first_result['device_row_counts'].split(', ')
                    for count in counts:
                        device, rows = count.split(': ')
                        total_rows = int(rows)
                        device_counts[device] = {
                            'total': total_rows,
                            'total_pages': (total_rows + int(page_size) - 1) // int(page_size)
                        }
                
                # 为每个表添加分页信息
                for table in result_json['tables']:
                    table_name = table['table_name']
                    if table_name in device_counts:
                        table['pagination'] = {
                            'current_page': int(page),
                            'page_size': int(page_size),
                            'total': device_counts[table_name]['total'],
                            'total_pages': device_counts[table_name]['total_pages']
                        }
                
                return result_json
        except Exception as e:
            print(f"Error in get_experiment_data: {str(e)}")
            raise

    def get_experiment_parameters(self, experiment_id: str) -> Dict:
        """
        获取实验参数信息
        Args:
            experiment_id: 实验ID
        Returns:
            Dict: 包含实验参数和保护阈值的字典
        """
        connection = self.get_connection()
        try:
            with connection.cursor(pymysql.cursors.DictCursor) as cursor:
                # 调用存储过程
                cursor.callproc('get_experiment_parameters', (experiment_id,))
                
                # 初始化结果字典
                result_json = {}
                all_results = []
                
                # 获取所有结果集
                while True:
                    results = cursor.fetchall()
                    if results:
                        all_results.append(results)
                    if not cursor.nextset():
                        break
                
                if all_results:
                    # 处理实验ID
                    result_json['experiment_id'] = all_results[0][0]['experiment_id']
                    
                    # 处理实验参数
                    if len(all_results) > 1 and all_results[1]:
                        result_json['experiment_parameters'] = all_results[1][0]
                    
                    # 处理通道保护阈值
                    if len(all_results) > 2 and all_results[2]:
                        result_json['channel_protections'] = [
                            {
                                'channel_name': row['channel_name'],
                                'channel_protection_threshold': float(row['channel_protection_threshold'])
                                if row['channel_protection_threshold'] is not None
                                else None
                            }
                            for row in all_results[2]
                        ]
                
                return result_json
                    
        except Exception as e:
            print(f"Error getting experiment parameters: {e}")
            raise


# 创建单例实例
db_manager = DatabaseManager() 